package packageGenerateData;

import packageInputData.DbConnection;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

import routine.FunctionLib;

public class GenerateDataZHD {


	/**
	 * function 
	 * to generate ZHD data and put it into the database
	 * @return			void
	*/
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static void generateZHD()
	{
		
		
		double lat=0,alt=0,zhd=0,sigZhd=0;
		List 	list1 = new ArrayList(),
				list2 = new ArrayList(),
				list3 = new ArrayList();
		
		String siteName="",req="";
		int inserts=0;
		
		double deg2rad=Math.atan(1)/45;
		double m2km=(double)1/(double)1000;
		
		//connect to the data base
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
	  	//get the doy from table "param"
		int paramDoy= 	(Integer)(DbConnection.getFromDB("doy", "param"));
	
		//get GPS code list from sites_gps in list1 then in data[]
		try {
		       list1 =(List) qRunner.query(conn,
							    		   "select code from sites_gps",
							    		   new ArrayListHandler());
		} catch (SQLException e2) {
		       e2.printStackTrace();
		}
		//loop gps sites
		for(int j=0;j<list1.size();j++)
		{
			Object data[] = (Object[]) list1.get(j);
			siteName=data[0].toString().toUpperCase();
			//System.out.println("Station in process: "+siteName);
			//execute query
			try {
			       list2 =(List) qRunner.query(conn,
								    		   "select lat,alt from sites_all where code=?",
								    		   siteName,
								    		   new ArrayListHandler());
			} catch (SQLException e2) {
			       e2.printStackTrace();
			}
			//get the result
			for(int i=0;i<list2.size();i++)
			{
				Object data2[] = (Object[]) list2.get(i);
				
				lat=Double.parseDouble(data2[0].toString());
				alt=Double.parseDouble(data2[1].toString());
				
				lat*=deg2rad;
				alt*=m2km;		
				siteName=data[0].toString().toUpperCase();
				//execute query
				try {
				       list3 =(List) qRunner.query(conn,
									    		   "select pres,hr,m from ptu2 where site=?  and session_id= "+MainClass.session,
									    		   siteName,
									    		   new ArrayListHandler());
				} catch (SQLException e2) {
				       e2.printStackTrace();
				}
				//get the result
				if(list3.size()>0)
				{
					for(int k=0;k<list3.size();k++)
					{
						Object data3[] = (Object[]) list3.get(k);
						
						zhd=	(Double.parseDouble(data3[0].toString())*
								0.002277)/
								FunctionLib.ffun(lat, alt);
						
						sigZhd=	(((Double.parseDouble(data3[0].toString())*
								0.002277)*0.0006)/
								FunctionLib.ffun(lat, alt));
				
						//prepare query						
						req="INSERT INTO zhd" +
								"(session_id," +
								"doy,hr,mn," +
								"zhd,sigZhd," +
								"site)" +				
								"VALUES (?,?,?,?,?,?,?)";
						//execute query
						 try {
								inserts = qRunner.update(conn,req,
											MainClass.session,
											paramDoy,
											Integer.parseInt(data3[1].toString()),
											Integer.parseInt(data3[2].toString()),
											zhd,
											sigZhd,
											siteName);
						 } catch (SQLException e) {									
								e.printStackTrace();
						 }
					
					}
				}
			}
			//end loop gps list
		}
		//check if insertion is done properly
		 if (inserts==1)
			{
 				System.out.println("Insertion in zhd  done ");
			}
		
	}
}
